﻿Imports System.Data.OleDb
Public Class QuanLyNoDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll(MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select * from QuanLyNo where MaUser = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaUser", OleDbType.Integer, 11).Value = MaUser
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function TraNo(MaNo As Integer) As Boolean
        Dim DaThanhToan As Integer = 0
        Dim query As String = "select SoTien,MaNganHang,MaChiPhi,MaUser from QuanLyNo where MaNo = ? AND DaThanhToan = 0"
        Try
            Dim conn As New OleDbConnection(conn_str)
            Dim cmd As New OleDbCommand(query, conn)
            cmd.Parameters.AddWithValue("@MaNo", MaNo)
            conn.Open()
            Dim reader As OleDbDataReader = cmd.ExecuteReader()
            If reader.HasRows Then
                Dim dt = DB_Provider.Read_schema("PhieuChi")
                Dim dr As DataRow = Nothing
                While reader.Read()
                    dr = dt.NewRow()
                    dr.Item("Ngay") = Date.Now.ToString("yyyy-MM-dd")
                    dr.Item("SoTien") = Double.Parse(reader.GetValue(0))
                    dr.Item("MaUserChi") = reader.GetInt32(3)
                    dr.Item("MaUserLap") = MainForm.current_user.MaUser
                    dr.Item("MaNganHang") = reader.GetInt32(1)
                    dr.Item("MaChiPhi") = reader.GetInt32(2)
                    dr.Item("ChiTiet") = "tra_no"
                    dt.Rows.Add(dr)                    
                End While
                reader.Close()
                DaThanhToan = DB_Provider.Write(dt, "PhieuChi")
                cmd.CommandText = "update QuanLyNo set DaThanhToan = ? where MaNo = ?"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@DaThanhToan", DaThanhToan)
                cmd.Parameters.AddWithValue("@MaNo", MaNo)
                cmd.ExecuteNonQuery()
                cmd.CommandText = "update TaiKhoanNganHang set sotien = sotien + ? where MaUser = ? AND MaNganHang = ?"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@sotien", dr.Item("SoTien"))
                cmd.Parameters.AddWithValue("@MaUser", dr.Item("MaUserChi"))
                cmd.Parameters.AddWithValue("@MaNganHang", dr.Item("MaNganHang"))
                cmd.ExecuteNonQuery()
            End If
            conn.Close()
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return DaThanhToan > 0
    End Function
    Shared Function PhatSinhChi(ByVal MaUser As Integer) As DataTable
        Dim dt As New DataTable
        Dim tien_lai As Double
        Dim query As String = "select *,dateadd(mm,KyHan,NgayMuon) NgayDaoHan from QuanLyNo where MaUser = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.AddWithValue("@MaUser", MaUser)
            adapter.Fill(dt)
            If dt.Rows.Count > 0 Then
                Dim str As String = "SELECT count(*) FROM PhieuChi where ChiTiet = ? and MaUserChi = ? and MONTH(Ngay) = MONTH(CURRENT_TIMESTAMP)"
                Dim conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(str, conn)
                cmd.Parameters.Add("@ChiTiet", OleDbType.VarWChar, 250).Value = "tra_lai_no"
                cmd.Parameters.Add("@MaUserChi", OleDbType.Integer, 11).Value = MaUser
                conn.Open()
                Dim count As Integer = cmd.ExecuteScalar()
                conn.Close()
                If count = 0 Then
                    Dim tmp As DataRow                    
                    Dim dt_phieuchi As DataTable = DB_Provider.Read_schema("PhieuChi")
                    For Each dr As DataRow In dt.Rows
                        'Neu tu dong gia han thi tinh lai tung thang, neu khong thi khong tinh lai
                        If dr("TinhTrang") Or (dr("TinhTrang") = 0 And dr("NgayMuon") <= Date.Now.ToShortDateString() And Date.Now.ToShortDateString() <= dr("NgayDaoHan")) Then
                            If dr("DaThanhToan") = 0 Then
                                tmp = dt_phieuchi.NewRow()
                                tien_lai = dr.Item("SoTien") * (dr.Item("LaiSuat") / 100) / 12
                                tmp.Item("SoTien") = tien_lai
                                tmp.Item("Ngay") = Date.Now.ToString("yyyy-MM-dd")
                                tmp.Item("MaUserChi") = MaUser
                                tmp.Item("MaUserLap") = MaUser
                                tmp.Item("MaNganHang") = dr.Item("MaNganHang")
                                tmp.Item("MaChiPhi") = dr.Item("MaChiPhi")
                                tmp.Item("ChiTiet") = "tra_lai_no"
                                dt_phieuchi.Rows.Add(tmp)
                                TaiKhoanNganHangDAO.TruTien(MaUser, dr.Item("MaNganHang"), tien_lai)
                            End If
                        End If
                    Next
                    DB_Provider.Write(dt_phieuchi, "PhieuChi")
                End If
            End If
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
End Class
